"""Move File to Database

.. helpdoc::

Reads files from a text file and brings them into RedR in the form of an SQLite structure.  These files should be like a table and should have values that are separated either by a tab, space, or comma.  You can use the scan feature to scan a small part of your data and make sure that it is in the correct format.  You can also set a column to represent the row names of your data.  This is encouraged if you have row names as some widgets rely on row names to help them function.  By specifying a column as a rownames column that column will have the reserved word of Rownames no matter what the column name is in the data table that you are reading.  SQLite tables can be connected to any RDataFrame or RList accepting widget.

""" 


"""<widgetXML>
    <name>
        Move File to Database
    </name>
    <icon>
        readfile.png
    </icon>
    <summary>
        Runs the query 'QUERY', sets a new view as the result of that query and then sends it through the output slot.
    </summary>
    <tags>
        <tag priority="0">
            SQLite
        </tag>
    </tags>
    <author>
        <authorname>Kyle R. Covington</authorname>
        <authorcontact>kyle@red-r.org</authorcontact>
    </author>
    </widgetXML>
"""


from OWRpy import *
import redRGUI 

import re, os
import textwrap
import cPickle
import pickle
import types
import sqlite3, redR
import redREnviron
import redRi18n
_ = redRi18n.get_(package = 'base')
class readSQLiteFile(OWRpy):
    def __init__(self, **kwargs):

        OWRpy.__init__(self, **kwargs)
        self.require_librarys(['RSQLite'])
        self.recentFiles=['Select File']
        self.colClasses = []
        self.myColClasses = []
        self.colNames = []
        self.dataTypes = []
        self.useheader = 1
        #set R variable names        
        self.database = None
        self.loadFileName = ''
        
        self.setRvariableNames(['dataframe_org','dataframe_final','filename', 'parent', 'table'])
        
        self.inputs.addInput('database', "Attached Database", signals.RedRSQLite.SQLiteDatabase, self.setDB)
        self.outputs.addOutput("data.frame", "SQLite Database", signals.RedRSQLite.SQLiteTable)
        #GUI
        area = redRGUI.base.widgetBox(self.controlArea,orientation='horizontal')       
        #area.setSizePolicy(QSizePolicy(QSizePolicy.MinimumExpanding ,QSizePolicy.MinimumExpanding))
        #area.layout().setAlignment(Qt.AlignTop)
        options = redRGUI.base.widgetBox(area,orientation='vertical')
        options.setMaximumWidth(300)
        # options.setMinimumWidth(300)
        options.setSizePolicy(QSizePolicy.Fixed,QSizePolicy.Fixed)
        area.layout().setAlignment(options,Qt.AlignTop)
        
        self.dbinfo = redRGUI.base.widgetLabel(options, 'No Database Set')
        self.fileLabel = redRGUI.base.widgetLabel(options, 'No File Set')
        box2 = redRGUI.base.widgetBox(options)
        
        """.. rrgui::"""
        self.tableNameEdit = redRGUI.base.lineEdit(box2, label = 'Table Name', toolTip = 'Sets the name of the table in the database')
        
        self.tableNameEdit.setText(self.Rvariables['table'].replace('.', '_'))
        
        redRGUI.base.button(options, label = "Browse File", callback = self.browseFile)
        
        """.. rrgui::"""
        self.delimiter = redRGUI.base.radioButtons(options, label='Column Seperator',
        buttons = [('\t', 'Tab'), (' ', 'Space'), (',', 'Comma')], setChecked='\t',callback=self.scanNewFile,
        orientation='horizontal')

        """.. rrgui::"""
        box = redRGUI.base.groupBox(options, label="Row and Column Names", 
        addSpace = True, orientation ='horizontal')

        """.. rrgui::"""
        self.hasHeader = redRGUI.base.checkBox(box, label = 'Header', displayLabel = False, buttons = ['Column Headers'],setChecked=['Column Headers'],toolTips=['a logical value indicating whether the file contains the names of the variables as its first line. If missing, the value is determined from the file format: header is set to TRUE if and only if the first row contains one fewer field than the number of columns.'],
        orientation='vertical',callback=self.scanNewFile)
        
        
        box = redRGUI.base.groupBox(options, label="Other Options", 
        addSpace = True, orientation ='vertical')
        # box.setSizePolicy(QSizePolicy.Minimum, QSizePolicy.Minimum)
        split = redRGUI.base.widgetBox(box,orientation='horizontal')
        box2 = redRGUI.base.widgetBox(split,orientation='vertical')
        #box2.setSizePolicy(QSizePolicy.Fixed,QSizePolicy.Fixed)
        split.layout().setAlignment(box2,Qt.AlignTop)
        #self.quote = redRGUI.lineEdit(box2,text='"',label='Quote:', width=50, orientation='horizontal')
        
        """.. rrgui::"""
        self.decimal = redRGUI.base.lineEdit(box2, text = '.', label = 'Decimal:', width = 50, orientation = 'horizontal', toolTip = 'Decimal sign, some countries may want to use the \'.\'')
        
        """.. rrgui::"""
        self.numLinesScan = redRGUI.base.lineEdit(box2,text='10',label='# Lines to Scan:',width=50,orientation='horizontal')
        
        
        holder = redRGUI.base.widgetBox(options,orientation='horizontal')
        rescan = redRGUI.base.button(holder, label = 'Rescan File', callback = self.scanNewFile)
        load = redRGUI.base.button(holder, label = 'Load File', callback = self.loadFile)
        holder.layout().setAlignment(Qt.AlignRight)

        self.tableArea = redRGUI.base.groupBox(area)
        self.tableArea.setMinimumWidth(200)
        #self.tableArea.setHidden(True)
        self.tableArea.setSizePolicy(QSizePolicy.MinimumExpanding,QSizePolicy.MinimumExpanding)

        self.scanarea = redRGUI.base.textEdit(self.tableArea, label = 'Scan Area')
        self.scanarea.setLineWrapMode(QTextEdit.NoWrap)
        self.scanarea.setReadOnly(True)
        
        scroll = redRGUI.base.scrollArea(self.tableArea)
        self.colClasses = redRGUI.base.widgetBox(self.tableArea)
        scroll.setWidget(self.colClasses)
        self.colClasses.layout().setSizeConstraint(QLayout.SetMinAndMaxSize)
        #self.setFileList()

    def loadCustomSettings(self,settings):
        if not self.filecombo.getCurrentFile():
            redRGUI.base.widgetLabel(self.browseBox,label=_('The loaded file is not found on your computer.\nBut the data saved in the Red-R session is still available.')) 
        for i in range(len(self.myColClasses)):
            s = redRGUI.comboBox(self.columnTypes, items = ['factor','numeric','character','integer','logical'], orientation = 'horizontal', callback = self.updateColClasses)
            index = s.findText(self.myColClasses[i])
            if index != -1:
                s.setCurrentIndex(index)
            s.setEnabled(False)
            q = redRGUI.widgetLabel(self.columnTypes,label=self.colNames[i])
            self.columnTypes.layout().addWidget(s, i, 1)
            self.columnTypes.layout().addWidget(q, i, 0)
        
    def setDB(self, data):
        """Set the database used by the widget for dumping data.  This is obtained from a connection to the SQLiteDatabase signal"""
        if data:
            self.database = data.getData()
            self.dbinfo.setText('Database File:'+unicode(self.database))
        else:
            self.database = None
            self.dbinfo.setText('No database connected')
    def browseFile(self): 
        """Browse a new file and scan"""
        fn = QFileDialog.getOpenFileName(self, "Open File", redREnviron.settings['workingDir'],
        "Text file (*.txt *.csv *.tab);; All Files (*.*)")
        if fn.isEmpty(): return
        fn = unicode(fn)
        redREnviron.settings['workingDir'] = os.path.split(fn)[0]

        self.fileLabel.setText(os.path.split(fn)[1])
        self.loadFileName = fn
        
        self.saveGlobalSettings()
        self.scanNewFile()

    def scanNewFile(self):
        """Clear the settings and begin the scan"""
        self.removeInformation()
        self.removeWarning()

        self.colNames = []
        self.dataTypes = []
        self.loadFile(scan=True)
    
    def updateColClasses(self):
        """Updates the myColClasses argument to be a list of tuples of col names and SQLite types"""
        self.myColClasses = []
        for i in self.dataTypes:
            self.myColClasses.append((i[0], i[1].getCheckedId()))
    def getColClasses(self):
        t = ''
        t += ','.join(['%s = "%s"' % i for i in self.myColClasses if i[1] != 'AUTO'])
        return t
    def scanFile(self):
        self.loadFile(scan=True)

        
    def loadFile(self,scan=False):
        
        if self.loadFileName == '':
            self.status.setStatus(0)
        if not os.path.isfile(self.loadFileName): # check if the file exists and can be read
            QMessageBox.information(self,'Error', "File does not exist.", 
            QMessageBox.Ok + QMessageBox.Default)
            self.status.setStatus(0)
            return
            
        d = {'SEP':self.delimiter.getCheckedId(), 'TABLE':self.tableNameEdit.text(), 'FILE':self.loadFileName}
        
        if 'local|' in self.database:
            d['DATABASE'] = os.path.join(redREnviron.directoryNames['tempDir'], self.database.split('|')[1]).replace('\\', '/')
        else:
            d['DATABASE'] = self.database.replace('\\', '/')
            
        
        
        if not scan: 
            
            self.R('tempCon<-dbConnect("SQLite", "%(DATABASE)s")' % d, wantType = redR.NOCONVERSION)
            if d['TABLE'] in self.R('dbListTables(tempCon)', wantType = redR.LIST):
                mb = QMessageBox(_("RedRSQLite"), _("The table you want to create already exists.\n\nWould you like to remove it?"), 
                QMessageBox.Information, QMessageBox.Yes, 
                QMessageBox.No | QMessageBox.Escape | QMessageBox.Default, QMessageBox.NoButton, self)
                
                if mb.exec_() == QMessageBox.Yes:
                    self.R('dbRemoveTable(tempCon, "%(TABLE)s")' % d, wantType = redR.NOCONVERSION)
                else:
                    return
            classes = self.getColClasses()
            if classes != '':
                d['COLCLASSES'] = 'list(%s)' % self.getColClasses()
                self.R('dbWriteTable(tempCon, name = "%(TABLE)s", value = "%(FILE)s", sep = "%(SEP)s", header = T, row.names = F, field.types = %(COLCLASSES)s)' % d, wantType = redR.NOCONVERSION)
            else:
                self.R('dbWriteTable(tempCon, name = "%(TABLE)s", value = "%(FILE)s", sep = "%(SEP)s", header = T, row.names = F)' % d, wantType = redR.NOCONVERSION)
            self.R('dbDisconnect(tempCon)', wantType = redR.NOCONVERSION)
            self.commit()
        else:
            self.scanarea.clear()
            self.scanarea.insertPlainText(self.R('paste(capture.output(read.table("%(FILE)s", header = T, sep = "%(SEP)s", nrows = 10)), collapse ="\n")' % d, wantType = redR.CONVERT))
            self.updateScan(d)
        
        
        
    def updateScan(self, d):
        dataTypes = ['AUTO', 'INTEGER', 'REAL', 'TEXT', 'BLOB']
        self.dataTypes = []
        for i in self.colClasses.findChildren(QWidget):
            i.setHidden(True)
        for n in self.R('names(read.table("%(FILE)s", header = T, sep = "%(SEP)s", nrows = 10))' % d, wantType = redR.LIST):
            s = redRGUI.base.radioButtons(self.colClasses,label=n,
                buttons=dataTypes,orientation='horizontal',callback=self.updateColClasses, setChecked = 'AUTO')
            self.dataTypes.append([n,s])
        
    def updateGUI(self):
        pass
    def commit(self):
        self.updateGUI()
       
        sendData = signals.RedRSQLite.SQLiteTable(self, data = unicode(self.tableNameEdit.text()), database = self.database)
        self.rSend("data.frame", sendData)
        